package com.hjm.project.report.report.service.impl;

import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.google.common.collect.Sets;
import com.hjm.common.utils.ExportExcelUtil;
import com.hjm.common.utils.request.AutoLoadData;
import com.hjm.project.report.report.domain.ReportMerchant;
import com.hjm.project.report.report.mapper.ReportMerchantMapper;
import com.hjm.project.report.report.service.ReportMerchantService;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.Async;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.ObjectUtils;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.text.DecimalFormat;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;

/**
 * @author puyiliang
 * @date create in 2021/1/16 17:21
 */
@Service
@SuppressWarnings("all")
public class ReportMerchantServiceImpl implements ReportMerchantService {

    @Autowired
    private ReportMerchantMapper reportMerchantMapper;

    @Autowired
    private ReportMerchantTask reportMerchantTask;

    @Override
    public List<ReportMerchant> selectQueryMerchantList(Map<String, String> request) {
        return reportMerchantMapper.selectQueryMerchantList(request);
    }

    @Override
    @Async
    @Transactional(rollbackFor = Exception.class)
    public void saveUploadMerchant(String fileUrl, String loginName, String source) {
        Map<String, String> responseMap = Maps.newHashMap();
        responseMap.put("operatorType", "3");
        responseMap.put("fileName", "operation");
        responseMap.put("addMan", loginName);
        responseMap.put("status","2");
        Workbook wb = null;
        InputStream is = null;
        try {
            is = new FileInputStream(fileUrl);
            String ext = fileUrl.substring(fileUrl.lastIndexOf("."));
            if (".xls".equals(ext)) {
                wb = new HSSFWorkbook(is);
            } else if (".xlsx".equals(ext)) {
                wb = new XSSFWorkbook(is);
            }
            int numberOfSheets = wb.getNumberOfSheets();
            if (numberOfSheets < 2){
                responseMap.put("operatorContent","无商编对应终端页");
            }else{
                Sheet bankInfoSheet = wb.getSheetAt(0);
                Sheet terminalSheet = wb.getSheetAt(1);
                Row row = null;
                //首先获取所有的商编对应的终端
                Map<String, Set<String>> terminalMap = Maps.newHashMap();
                for(int i = 2; i <= terminalSheet.getLastRowNum(); i++){
                    row = terminalSheet.getRow(i);
                    String merchantNo = getCellValue(row.getCell(0));
                    String terminalNo = getCellValue(row.getCell(1));
                    if (StringUtils.isNotEmpty(merchantNo)){
                        Set<String> terminalSet = terminalMap.get(merchantNo);
                        if (ObjectUtils.isEmpty(terminalSet)){
                            terminalSet = Sets.newHashSet();
                            terminalMap.put(merchantNo, terminalSet);
                        }
                        terminalSet.add(terminalNo);
                    }
                }
                AtomicInteger success = new AtomicInteger(0);
                AtomicInteger fail = new AtomicInteger(0);
                List<Map<String, Object>> failList = Lists.newArrayList();

                //获取商编信息
                for (int i = 2; i <= bankInfoSheet.getLastRowNum(); i++) {
                    row = bankInfoSheet.getRow(i);
                    if (row == null){
                        break;
                    }
                    Map<String, String> data = assembleBankInfoData(row);
                    try {
                        //开启新的事务做提交，保证一个事物中的数据一致
                        reportMerchantTask.insertBankInfo(terminalMap, data, source, loginName);
                        success.incrementAndGet();
                    }catch (Exception e){
                        e.printStackTrace();
                        fail.incrementAndGet();
                        Map<String, Object> errorMap = Maps.newHashMap();
                        errorMap.put("mer_no", data.get("bno"));
                        errorMap.put("mer_name", data.get("note"));
                        errorMap.put("cao_type", "钱宝商编导入");
                        errorMap.put("cao_status", e.getMessage());
                        failList.add(errorMap);
                    }
                }

                if (failList.size() != 0){
                    LinkedHashMap<String, String> titleMap = getFailTitleMap();
                    Map<String, Object> workMap = ExportExcelUtil.exportListExcelClearExcel(null, failList, titleMap, "失败商编");
                    String fileName = System.currentTimeMillis()+"_失败文件.xlsx";
                    responseMap.put("fileName", fileName);
                    responseMap.put("operatorContent","商编入库部分数据操作失败，详情请下载excel");
                    ExportExcelUtil.exportExcel(workMap, fileName, AutoLoadData.filetemp);
                }else{
                    responseMap.put("operatorContent", "商编入库成功"+success+"条");
                }
                responseMap.put("status","0");
            }
        }catch (Exception e){
            e.printStackTrace();
            responseMap.put("operatorContent","出现异常,联系技术人员");
            responseMap.put("status","1");
        }finally {
            try {
                wb.close();
            } catch (IOException e) {}
            try {
                is.close();
            } catch (IOException e) {}
        }
        reportMerchantMapper.insertJobTask(responseMap);
    }

    @Override
    @Async
    @Transactional(rollbackFor = Exception.class)
    public void downloadMerchant(String loginName, Map<String, String> request) {
        Map<String, String> responseMap = Maps.newHashMap();
        responseMap.put("operatorType", "3");
        responseMap.put("addMan", loginName);
        responseMap.put("status","2");
        try {
            List<Map<String, Object>> merchants = reportMerchantMapper.selectDownloadMerchantList(request);
            LinkedHashMap<String, String> titleMap = getTitleMap();
            Map<String, Object> workMap = ExportExcelUtil.exportListExcelClearExcel(null, merchants, titleMap, "正式商编");
            String fileName = System.currentTimeMillis()+"_正式商编.xlsx";
            responseMap.put("fileName", fileName);
            ExportExcelUtil.exportExcel(workMap, fileName, AutoLoadData.filetemp);
            responseMap.put("operatorContent","商编导出完成");
            responseMap.put("status","0");
        }catch (Exception e){
            e.printStackTrace();
            responseMap.put("operatorContent","出现异常,联系技术人员");
            responseMap.put("status","1");
        }
        reportMerchantMapper.insertJobTask(responseMap);
    }

    @Override
    public List<Map<String, String>> getJobTaskList(Map<String, String> request) {
        return reportMerchantMapper.getJobTaskList(request);
    }

    @Override
    public void downloadFile(String id, HttpServletResponse response) {
        FileInputStream is = null;
        BufferedInputStream bs = null;
        ServletOutputStream os = null;
        BufferedOutputStream outputStream = null;
        try{
            String fileName = reportMerchantMapper.getFileNameById(id);
            String filePath = AutoLoadData.filetemp + File.separator + fileName;
            File file = new File(filePath);
            is = new FileInputStream(file);
            bs = new BufferedInputStream(is);
            os = response.getOutputStream();
            outputStream = new BufferedOutputStream(os);
            response.setCharacterEncoding("utf-8");
            response.setContentType("multipart/form-data");
            response.setHeader("Content-Disposition", "attachment;fileName=" + java.net.URLEncoder.encode(fileName,"utf-8"));
            byte[] buffer = new byte[1024];
            int bytesRead = 0;
            while ((bytesRead = bs.read(buffer)) != -1) {
                outputStream.write(buffer, 0, bytesRead);
            }
            outputStream.flush();
        }
        catch (Exception e){
            e.printStackTrace();
        }finally {
            if (outputStream != null){
                try {
                    outputStream.close();
                } catch (IOException e) {}
            }
            if (os != null){
                try {
                    os.close();
                } catch (IOException e) {}
            }
            if (bs != null){
                try {
                    bs.close();
                } catch (IOException e) {}
            }
            if (is != null){
                try {
                    is.close();
                } catch (IOException e) {}
            }
        }
    }

    @Override
    @Async
    @Transactional(rollbackFor = Exception.class)
    public void saveEditMerchant(String fileUrl, String loginName) {
        Map<String, String> responseMap = Maps.newHashMap();
        responseMap.put("operatorType", "3");
        responseMap.put("addMan", loginName);
        responseMap.put("fileName", "operation");
        responseMap.put("status","2");
        AtomicInteger success = new AtomicInteger(0);
        AtomicInteger fail = new AtomicInteger(0);
        List<Map<String, Object>> failList = Lists.newArrayList();
        Workbook wb = null;
        InputStream is = null;
        try {
            is = new FileInputStream(fileUrl);
            String ext = fileUrl.substring(fileUrl.lastIndexOf("."));
            if (".xls".equals(ext)) {
                wb = new HSSFWorkbook(is);
            } else if (".xlsx".equals(ext)) {
                wb = new XSSFWorkbook(is);
            }
            Sheet bankInfoSheet = wb.getSheetAt(0);
            if (bankInfoSheet.getLastRowNum() > 10000){
                responseMap.put("operatorContent","商编数量超过10000条,无法导入");
            }
            Row row = null;
            //获取商编信息
            for (int i = 1; i <= bankInfoSheet.getLastRowNum(); i++) {
                row = bankInfoSheet.getRow(i);
                if (row == null){
                    break;
                }
                //组装数据
                Map<String, String> data = assembleEditBankInfoData(row);
                try {
                    //开启新的事务做提交，保证一个事物中的数据一致
                    int i1 = reportMerchantTask.saveEditBankInfo(data);
                    if (i1 <= 0) {
                        fail.incrementAndGet();
                        Map<String, Object> errorMap = Maps.newHashMap();
                        errorMap.put("mer_no", data.get("bno"));
                        errorMap.put("mer_name", data.get("note"));
                        errorMap.put("cao_type", "正式商编信息变更");
                        errorMap.put("cao_status", "");
                        failList.add(errorMap);
                    } else {
                        success.incrementAndGet();
                    }
                }catch (Exception e){
                    e.printStackTrace();
                    fail.incrementAndGet();
                    Map<String, Object> errorMap = Maps.newHashMap();
                    errorMap.put("mer_no", data.get("bno"));
                    errorMap.put("mer_name", data.get("note"));
                    errorMap.put("cao_type", "正式商编信息变更");
                    errorMap.put("cao_status", e.getMessage());
                    failList.add(errorMap);
                }
            }

            if (failList.size() != 0){
                LinkedHashMap<String, String> titleMap = getFailTitleMap();
                Map<String, Object> workMap = ExportExcelUtil.exportListExcelClearExcel(null, failList, titleMap, "更新失败商编");
                String fileName = System.currentTimeMillis()+"_失败文件.xlsx";
                responseMap.put("fileName", fileName);
                responseMap.put("operatorContent","商编更新部分数据操作失败，详情请下载excel");
                ExportExcelUtil.exportExcel(workMap, fileName, AutoLoadData.filetemp);
            }else{
                responseMap.put("operatorContent", "商编更新成功"+success+"条");
            }
            responseMap.put("status","0");

        }catch (Exception e){
            e.printStackTrace();
            responseMap.put("status","1");
            responseMap.put("operatorContent","出现异常,联系技术人员");
        }finally {
            try {
                wb.close();
            } catch (IOException e) {}
            try {
                is.close();
            } catch (IOException e) {}
        }
        reportMerchantMapper.insertJobTask(responseMap);
    }

    @Override
    public List<Map<String, Object>> getTransactionDetailList(Map<String, String> request) {
        return reportMerchantMapper.getTransactionDetailList(request);
    }

    @Override
    @Async
    public void downloadTransactionDetail(String loginName, Map<String, String> request) {
        Map<String, String> responseMap = Maps.newHashMap();
        responseMap.put("operatorType", "3");
        responseMap.put("addMan", loginName);
        responseMap.put("status","2");
        try {
            List<Map<String, Object>> transactionDetail = reportMerchantMapper.getTransactionDetailList(request);
            //处理数据
            handleTransactionDetail(transactionDetail);
            LinkedHashMap<String, String> titleMap = getTransactionTitleMap();
            Map<String, Object> workMap = ExportExcelUtil.exportListExcelClearExcel(null, transactionDetail, titleMap, "商编交易");
            String fileName = System.currentTimeMillis()+"_商编交易.xlsx";
            responseMap.put("fileName", fileName);
            ExportExcelUtil.exportExcel(workMap, fileName, AutoLoadData.filetemp);
            responseMap.put("operatorContent","商编交易导出完成");
            responseMap.put("status","0");
        }catch (Exception e){
            e.printStackTrace();
            responseMap.put("status","1");
            responseMap.put("operatorContent","出现异常,联系技术人员");
        }
        reportMerchantMapper.insertJobTask(responseMap);
    }

    private void handleTransactionDetail(List<Map<String, Object>> transactionDetail){
        for (Map<String, Object> map : transactionDetail) {
            String status = String.valueOf(map.get("STATUS"));
            String cardType = String.valueOf(map.get("CARDTYPE"));
            String settType = String.valueOf(map.get("SETT_TYPE"));
            String auState = String.valueOf(map.get("AU_STATE"));
            if ("0".equals(settType)){
                map.put("SETT_TYPE","D0");
            }else if ("1".equals(settType)){
                map.put("SETT_TYPE","T0");
            }
            if ("0".equals(auState)){
                map.put("AU_STATE","未清算");
            }else if ("1".equals(auState)){
                map.put("AU_STATE","已清算");
            }
            if ("0".equals(cardType)){
                map.put("CARDTYPE","未知");
            }else if ("1".equals(cardType)){
                map.put("CARDTYPE","借记卡");
            }else if ("2".equals(cardType)){
                map.put("CARDTYPE","贷记卡");
            }else if ("3".equals(cardType)){
                map.put("CARDTYPE","预付费卡");
            }


            if ("0".equals(status)){
                map.put("STATUS","成功");
            }else if ("1".equals(status)){
                map.put("STATUS","补录");
            }else if ("2".equals(status)){
                map.put("STATUS","失败");
            }else if ("-1".equals(status)){
                map.put("STATUS","撤销");
            }else if ("-2".equals(status)){
                map.put("STATUS","冲正");
            }
        }

    }

    private LinkedHashMap<String, String> getTransactionTitleMap(){
        LinkedHashMap<String, String> title = Maps.newLinkedHashMap();
        title.put("SERIAL","流水号");
        title.put("MER_NO","系统商户编号");
        title.put("MER_NAME","商户名称");
        title.put("AGENT_NO","服务商编号");
        title.put("SCREEN_NAME","结算人");
        title.put("MSGTYPE","交易类型");
        title.put("isStringTERNO","终端号");
        title.put("LOCALDATE","交易日期");
        title.put("LOCALTIME","交易时间");
        title.put("PAN","交易卡号");
        title.put("AMOUNT","交易金额");
        title.put("STATUS","交易状态");
        title.put("CARDTYPE","卡类型");
        title.put("SETT_TYPE","清算类型");
        title.put("AU_STATE","清算状态");
        title.put("BNO","渠道商户编号");
        return title;
    }

    private LinkedHashMap<String, String> getFailTitleMap(){
        LinkedHashMap<String, String> title = Maps.newLinkedHashMap();
        title.put("cao_type","操作");
        title.put("mer_no","商户编号");
        title.put("mer_name","商户名称");
        title.put("cao_status","错误代码");
        return title;
    }

    private LinkedHashMap<String, String> getTitleMap(){
        LinkedHashMap<String, String> title = Maps.newLinkedHashMap();
        title.put("BNO","商户编号");
        title.put("NOTE","商户名称");
        title.put("RATETYPE","费率类型");
        title.put("LOCALLEVEL","优先级别");
        title.put("STATUS","状态");
        title.put("MINMONEY","通道允许的最小金额");
        title.put("MAXMONEY","通道允许的最大金额");
        title.put("SUMMONEY","通道交易上限金额");
        title.put("COUNTSERIAL","通道交易上限笔数");
        title.put("AFTERH","可交易前时间");
        title.put("BEFORH","可交易后时间");
        title.put("BEFOREHOUR","可交易前时间");
        title.put("AFTERHOUR","可交易后时间");
        title.put("MCC","行业码");
        title.put("AREA","省");
        title.put("AREAS","市");
        title.put("AREAQ","区");
        title.put("REMARKS","变更操作说明");
        title.put("BANKBNO","渠道真实商编");
        title.put("NOTE2","备注2");
        title.put("ISUER","所属商编池");
        title.put("ISSHORT","是否短商户");
        return title;
    }

    private Map<String, String> assembleBankInfoData(Row row){
        Map<String, String> data = Maps.newHashMap();
        data.put("bno", getCellValue(row.getCell(0)));
        data.put("note", getCellValue(row.getCell(1)));
        data.put("ratetype", getCellValue(row.getCell(2)));
        data.put("isuer", getCellValue(row.getCell(3)));
        data.put("locallevel", getCellValue(row.getCell(4)));
        data.put("standardrate", getCellValue(row.getCell(5)));
        data.put("standardmoney", getCellValue(row.getCell(6)));
        data.put("singlerate", getCellValue(row.getCell(7)));
        data.put("singlemoney", getCellValue(row.getCell(8)));
        data.put("countserial", getCellValue(row.getCell(9)));
        data.put("summoney", getCellValue(row.getCell(10)));
        data.put("minmoney", getCellValue(row.getCell(11)));
        data.put("maxmoney", getCellValue(row.getCell(12)));
        data.put("borrowcard", getCellValue(row.getCell(13)));
        data.put("supportedcard", getCellValue(row.getCell(14)));
        data.put("ismis", getCellValue(row.getCell(15)));
        data.put("area", getCellValue(row.getCell(16)));
        data.put("areas", getCellValue(row.getCell(17)));
        data.put("areaq", getCellValue(row.getCell(18)));
        data.put("mcc", getCellValue(row.getCell(19)));
        data.put("tbid", getCellValue(row.getCell(23)));
        data.put("bankbno", getCellValue(row.getCell(24)));
        String key20 = getCellValue(row.getCell(20));
        String[] split = key20.split("-");
        int item = split[1].indexOf("凌晨");
        if (item != -1) {
            String substring = split[1].substring(2);
            data.put("afterh", split[0]);
            data.put("beforh", "24");
            data.put("beforehour", "0");
            data.put("afterhour", substring);
        } else {
            data.put("afterh", split[0]);
            data.put("beforh", split[1]);
            data.put("beforehour", "");
            data.put("afterhour", "");
        }
        data.put("isload","0");
        data.put("nowcountserial","0");
        data.put("nowsummoney","0.00");
        data.put("status","2");
        return data;
    }

    private Map<String, String> assembleEditBankInfoData(Row row){
        Map<String, String> data = Maps.newHashMap();
        data.put("bno", getCellValue(row.getCell(0)));
        data.put("note", getCellValue(row.getCell(1)));
        data.put("ratetype", getCellValue(row.getCell(2)));
        data.put("locallevel", getCellValue(row.getCell(3)));
        data.put("status", getCellValue(row.getCell(4)));
        data.put("minmoney", getCellValue(row.getCell(5)));
        data.put("maxmoney", getCellValue(row.getCell(6)));
        data.put("summoney", getCellValue(row.getCell(7)));
        data.put("countserial", getCellValue(row.getCell(8)));
        data.put("afterh", getCellValue(row.getCell(9)));
        data.put("beforh", getCellValue(row.getCell(10)));
        data.put("beforehour", getCellValue(row.getCell(11)));
        data.put("afterhour", getCellValue(row.getCell(12)));
        data.put("mcc", getCellValue(row.getCell(13)));
        data.put("area", getCellValue(row.getCell(14)));
        data.put("areas", getCellValue(row.getCell(15)));
        data.put("areaq", getCellValue(row.getCell(16)));
        data.put("remarks", getCellValue(row.getCell(17)));
        data.put("bankbno", getCellValue(row.getCell(18)));
        data.put("note2", getCellValue(row.getCell(19)));
        data.put("isuer", getCellValue(row.getCell(20)));
        data.put("isshort", getCellValue(row.getCell(21)));
        return data;
    }

    private String getCellValue(Cell cell){
        if (cell != null){
            // 判断当前Cell的Type
            switch (cell.getCellType()) {
                // 如果当前Cell的Type为NUMERIC
                case Cell.CELL_TYPE_NUMERIC:
                case Cell.CELL_TYPE_FORMULA: {
                    // 判断当前的cell是否为Date
                    if (DateUtil.isCellDateFormatted(cell)) {
                        // 如果是Date类型则，转化为Data格式
                        return String.valueOf(cell.getDateCellValue());
                    } else {
                        Object val = cell.getNumericCellValue();
                        if ((Double) val % 1 > 0)
                        {
                            val = new DecimalFormat("0.00000000").format(val);
                        }
                        else
                        {
                            val = new DecimalFormat("0").format(val);
                        }
                        return val.toString();
                    }
                }
                // 如果当前Cell的Type为STRING
                case Cell.CELL_TYPE_STRING: return cell.getRichStringCellValue().getString().trim();
                // 默认的Cell值
                default: return "";
            }
        }
        return "";
    }
}
